﻿-- =============================================
-- Author:		Andreas Könighofer (IT-eXperience)
-- Create date: 15.01.2009
-- Description:	Selects available machines in a given date range
-- =============================================
CREATE PROCEDURE [dbo].[ReadAvailableMachines] 
	-- Add the parameters for the stored procedure here
	@StartDate SMALLDATETIME
	, @EndDate SMALLDATETIME
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
	Resource.ResourceID
	, Resource.LocationID
	, Location.LocationName
	, RessourceName
	, Resource.MachineTypeID
	, MachineType.MachineTypeName
	FROM Resource
	, Location
	, MachineType
	WHERE
	Resource.LocationID = Location.LocationID
	AND Resource.MachineTypeID = MachineType.MachineTypeID
	AND ResourceID NOT IN (
		SELECT ResourceID
		FROM RentalService
		WHERE
		(StartDate BETWEEN @StartDate AND @EndDate) -- already rented
		OR (ReturnedDate BETWEEN @StartDate AND @EndDate) -- returned when already needed
		OR ((EndDate BETWEEN @StartDate AND @EndDate) AND ((ReturnedDate IS NULL) OR (ReturnedDate >= @StartDate))) -- still rented and not yet returned
		OR ((StartDate >= @StartDate) AND (EndDate <= @EndDate) AND ((ReturnedDate IS NULL) OR (ReturnedDate >= @StartDate))) -- rented and not yet returned
		)	
END

---- end script ReadAvailableMachine stored procedure ----

---- start script ReadCustomer stored procedure ----

